 #!/usr/bin/env python
# -*- coding: utf-8 -*-
"""
从Visual Crossing API获取真实的历史风速数据
"""

import sys
import requests
import logging
from pathlib import Path
from datetime import datetime, timedelta, date
from typing import List, Dict, Optional
import time
import os

# 添加项目根目录到Python路径
project_root = Path(__file__).parent.absolute()
sys.path.insert(0, str(project_root))

# 配置日志
logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s - %(levelname)s - %(message)s',
    handlers=[
        logging.FileHandler('real_windspeed_fix.log'),
        logging.StreamHandler()
    ]
)
logger = logging.getLogger(__name__)

def get_visual_crossing_api_key():
    """获取Visual Crossing API密钥"""
    try:
        from backend.config.weather_config import WeatherConfig
        api_key = WeatherConfig.get_api_key()
        if api_key and api_key != 'YOUR_VISUAL_CROSSING_API_KEY':
            return api_key
        else:
            logger.error("Visual Crossing API密钥未正确配置")
            return None
    except ImportError:
        logger.error("无法导入WeatherConfig")
        return None

def get_historical_windspeed(api_key: str, target_date: date, location: str = "Beijing,China") -> Optional[float]:
    """从Visual Crossing API获取历史风速数据"""
    try:
        # 构建API请求URL
        base_url = "https://weather.visualcrossing.com/VisualCrossingWebServices/rest/services/timeline"
        url = f"{base_url}/{location}/{target_date}/{target_date}"
        
        params = {
            'unitGroup': 'metric',
            'include': 'days',
            'key': api_key,
            'contentType': 'json'
        }
        
        logger.info(f"请求Visual Crossing API获取 {target_date} 的风速数据")
        
        response = requests.get(url, params=params, timeout=10)
        response.raise_for_status()
        
        data = response.json()
        
        if 'days' in data and len(data['days']) > 0:
            day_data = data['days'][0]
            windspeed = day_data.get('windspeed')
            
            if windspeed is not None:
                logger.info(f"成功获取 {target_date} 的风速数据: {windspeed} km/h")
                return float(windspeed)
            else:
                logger.warning(f"{target_date} 没有风速数据")
                return None
        else:
            logger.warning(f"{target_date} 没有天气数据")
            return None
            
    except requests.exceptions.RequestException as e:
        logger.error(f"API请求失败 {target_date}: {e}")
        return None
    except Exception as e:
        logger.error(f"获取风速数据失败 {target_date}: {e}")
        return None

def get_missing_windspeed_records():
    """获取缺失风速数据的记录"""
    try:
        from backend.config.database import get_db_session
        from backend.entities.weather_daily import WeatherDaily
        from sqlalchemy import and_
        
        db = get_db_session()
        try:
            # 查找有气象数据但缺失ws_max的记录
            missing_records = db.query(WeatherDaily).filter(
                and_(
                    WeatherDaily.t_max.isnot(None),  # 有气象数据
                    WeatherDaily.ws_max.is_(None)    # 但缺失风速
                )
            ).order_by(WeatherDaily.dt).all()
            
            logger.info(f"找到 {len(missing_records)} 条缺失风速数据的记录")
            return missing_records
            
        finally:
            db.close()
            
    except Exception as e:
        logger.error(f"获取缺失记录失败: {e}")
        return []

def update_windspeed_with_real_data():
    """使用真实API数据更新风速"""
    try:
        from backend.config.database import get_db_session
        from backend.entities.weather_daily import WeatherDaily
        
        # 获取API密钥
        api_key = get_visual_crossing_api_key()
        if not api_key:
            logger.error("无法获取API密钥，请检查配置")
            return
        
        # 获取缺失风速数据的记录
        missing_records = get_missing_windspeed_records()
        
        if not missing_records:
            logger.info("没有缺失风速数据的记录")
            return
        
        updated_count = 0
        total_records = len(missing_records)
        
        logger.info(f"开始更新 {total_records} 条记录的风速数据")
        
        for i, record in enumerate(missing_records, 1):
            try:
                # 获取真实风速数据
                windspeed = get_historical_windspeed(api_key, record.dt)
                
                if windspeed is not None:
                    # 更新数据库
                    db = get_db_session()
                    try:
                        # 重新查询记录以确保获取最新状态
                        db_record = db.query(WeatherDaily).filter(
                            WeatherDaily.id == record.id
                        ).first()
                        
                        if db_record and db_record.ws_max is None:
                            db_record.ws_max = windspeed
                            db_record.updated_at = datetime.now()
                            db.commit()
                            updated_count += 1
                            
                            logger.info(f"更新 {record.dt} 的风速数据: {windspeed} km/h")
                        else:
                            logger.info(f"跳过 {record.dt} (已有风速数据)")
                    
                    finally:
                        db.close()
                else:
                    logger.warning(f"无法获取 {record.dt} 的风速数据")
                
                # 显示进度
                if i % 10 == 0:
                    logger.info(f"进度: {i}/{total_records} ({i/total_records*100:.1f}%)")
                
                # API请求间隔，避免超过限制
                time.sleep(1)
                
            except Exception as e:
                logger.error(f"处理 {record.dt} 记录失败: {e}")
                continue
        
        logger.info(f"风速数据更新完成!")
        logger.info(f"总共更新: {updated_count} 条记录")
        logger.info(f"成功率: {updated_count/total_records*100:.1f}%")
        
    except Exception as e:
        logger.error(f"更新风速数据失败: {e}")

def verify_windspeed_data():
    """验证风速数据补充结果"""
    try:
        from backend.config.database import get_db_session
        from backend.entities.weather_daily import WeatherDaily
        from sqlalchemy import and_, func
        
        db = get_db_session()
        try:
            # 检查总记录数
            total_records = db.query(WeatherDaily).count()
            
            # 检查有风速数据的记录数
            records_with_windspeed = db.query(WeatherDaily).filter(
                WeatherDaily.ws_max.isnot(None)
            ).count()
            
            # 检查仍然缺失风速数据的记录数
            missing_records = db.query(WeatherDaily).filter(
                and_(
                    WeatherDaily.t_max.isnot(None),
                    WeatherDaily.ws_max.is_(None)
                )
            ).count()
            
            # 检查风速数据统计
            windspeed_stats = db.query(
                func.avg(WeatherDaily.ws_max).label('avg_windspeed'),
                func.min(WeatherDaily.ws_max).label('min_windspeed'),
                func.max(WeatherDaily.ws_max).label('max_windspeed')
            ).filter(WeatherDaily.ws_max.isnot(None)).first()
            
            logger.info("=" * 50)
            logger.info("风速数据验证结果:")
            logger.info(f"总记录数: {total_records}")
            logger.info(f"有风速数据的记录数: {records_with_windspeed}")
            logger.info(f"缺失风速数据的记录数: {missing_records}")
            logger.info(f"风速数据覆盖率: {records_with_windspeed/total_records*100:.1f}%")
            
            if windspeed_stats:
                logger.info(f"平均风速: {windspeed_stats.avg_windspeed:.1f} km/h")
                logger.info(f"最小风速: {windspeed_stats.min_windspeed:.1f} km/h")
                logger.info(f"最大风速: {windspeed_stats.max_windspeed:.1f} km/h")
            
            logger.info("=" * 50)
            
        finally:
            db.close()
            
    except Exception as e:
        logger.error(f"验证风速数据失败: {e}")

def main():
    """主函数"""
    logger.info("开始从Visual Crossing API获取真实历史风速数据...")
    
    try:
        # 第一步：获取真实风速数据并更新
        logger.info("第一步：获取真实风速数据并更新数据库")
        update_windspeed_with_real_data()
        
        # 第二步：验证结果
        logger.info("第二步：验证更新结果")
        verify_windspeed_data()
        
        logger.info("真实风速数据获取脚本执行完成!")
        
    except Exception as e:
        logger.error(f"脚本执行失败: {e}")
        sys.exit(1)

if __name__ == "__main__":
    main()